package org.gdstash.db;

import org.gdstash.description.BonusDetail;
import org.gdstash.description.DetailComposer;
import org.gdstash.file.ARZRecord;
import org.gdstash.file.DDSLoader;
import org.gdstash.file.GDParseException;
import org.gdstash.util.GDMsgFormatter;
import org.gdstash.util.GDMsgLogger;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.nio.ByteBuffer;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;

public class DBSkill {
    public static final String TABLE_NAME = "GD_SKILL";
    public static final String FIELD_ID = "SKILL_ID";
    public static final String TEMPLATE_MASTERY = "database/templates/skill_mastery.tpl";
    private static final int ROW_SKILL_ID = 1;
    private static final int ROW_REC_CLASS = 2;
    private static final int ROW_NAME = 3;
    private static final int ROW_DESCRIPTION = 4;
    private static final int ROW_MASTERY = 5;
    private static final int ROW_DEVOTION = 6;
    private static final int ROW_MASTERY_ID = 7;
    private static final int ROW_SKILL_TIER = 8;
    private static final int ROW_MAX_LEVEL = 9;
    private static final int ROW_BITMAP_DOWN_ID = 10;
    private static final int ROW_BITMAP_DOWN = 11;
    private static final int ROW_BITMAP_UP_ID = 12;
    private static final int ROW_BITMAP_UP = 13;
    private static final int ROW_SKILL_LEVEL = 14;
    private static final int ROW_SKILL_DURATION = 15;
    private static final int ROW_BONUS_INC = 16;
    private static final int ROW_MODIFIED = 17;
    private static final int ROW_BUFF_SKILL_ID = 18;
    private static final int ROW_PET_SKILL_ID = 19;
    private static final int ROW_CONVERT_IN = 20;
    private static final int ROW_CONVERT_OUT = 21;
    private static final int ROW_CONVERT_IN_2 = 22;
    private static final int ROW_CONVERT_OUT_2 = 23;
    private static final int ROW_DEPENDENCY_ALL = 24;
    private static final String CLASS_MODIFIER = "Skill_Modifier";
    private static final String CLASS_MODIFIER_PROJECTILE = "Skill_ProjectileModifier";
    private static final String CLASS_MODIFIER_COOLDOWN = "Skill_RefreshCooldownModifier";
    private static final String CLASS_MODIFIER_PET = "SkillSecondary_PetModifier";
    private static final String CLASS_TRANSMUTER = "Skill_Transmuter";
    private static final String CLASS_TRANSMUTER_PROJECTILE = "Skill_ProjectileTransmuter";
    private static final String CLASS_TRANSMUTER_PET = "Skill_SpawnPetTransmuter";
    private static ConcurrentHashMap<String, DBSkill> hashBuffer;
    private String skillID;
    private String recClass;
    private String name;
    private String description;
    private boolean mastery;
    private boolean devotion;
    private String masteryID;
    private int skillTier;
    private int maxLevel;
    private String bitmapDownID;
    private byte[] bitmapDown;
    private String bitmapUpID;
    private byte[] bitmapUp;
    private int level;
    private int duration;
    private boolean bonusIncrement;
    private boolean modified;
    private String buffSkillID;
    private String petSkillID;
    private String convertIn;
    private String convertOut;
    private String convertIn2;
    private String convertOut2;
    private boolean dependencyAll;
    private List<Integer> xpLevels;
    private List<DBStat> stats;
    private List<DBStatBonusRace> statBonusRaces;
    private List<DBSkillSpawn> spawnPets;
    private List<DBSkillDependency> dependencies;
    private List<DBSkillConnector> connections;
    private DBSkill dbBuffSkill;
    private DBSkill dbPetSkill;
    private int numXPLevels;
    private DBSkill dbMastery;
    private BufferedImage imageDown;
    private BufferedImage imageUp;

    public DBSkill() {
        this.skillID = null;
        this.recClass = null;
        this.name = null;
        this.description = null;
        this.mastery = false;
        this.devotion = false;
        this.masteryID = null;
        this.skillTier = 0;
        this.maxLevel = 0;
        this.bitmapDownID = null;
        this.bitmapDown = null;
        this.bitmapUpID = null;
        this.bitmapUp = null;
        this.level = 0;
        this.duration = 0;
        this.bonusIncrement = false;
        this.modified = false;
        this.buffSkillID = null;
        this.petSkillID = null;
        this.convertIn = null;
        this.convertOut = null;
        this.convertIn2 = null;
        this.convertOut2 = null;
        this.dependencyAll = false;
        this.numXPLevels = 0;
        this.xpLevels = null;
        this.stats = new LinkedList<DBStat>();
        this.statBonusRaces = new LinkedList<DBStatBonusRace>();
        this.spawnPets = new LinkedList<DBSkillSpawn>();
        this.dependencies = new LinkedList<DBSkillDependency>();
        this.connections = new LinkedList<DBSkillConnector>();
    }

    public DBSkill(final String name) {
        this();
        this.name = name;
    }

    public DBSkill(final String name, final String skillID) {
        this();
        this.name = name;
        this.skillID = skillID;
    }

    private DBSkill(final ARZRecord record) {
        this.skillID = record.getFileName();
        this.name = record.getSkillName();
        this.recClass = record.getRecordClass();
        this.description = record.getSkillDescription();
        this.mastery = record.isMastery();
        this.devotion = record.isDevotion();
        this.masteryID = null;
        this.skillTier = record.getSkillTier();
        this.maxLevel = record.getSkillMaxLevel();
        this.bitmapDownID = record.getSkillBitmapDownID();
        this.bitmapUpID = record.getSkillBitmapUpID();
        this.level = record.getSkillLevel();
        this.duration = record.getSkillDuration();
        this.bonusIncrement = record.hasSkillBonusIncrement();
        this.modified = record.hasSkillModifier();
        this.buffSkillID = record.getSkillBuffID();
        this.petSkillID = record.getPetBonusID();
        this.convertIn = record.getConversionIn();
        this.convertOut = record.getConversionOut();
        this.convertIn2 = record.getConversionIn2();
        this.convertOut2 = record.getConversionOut2();
        this.dependencyAll = record.isDependencyAll();
        this.xpLevels = record.getSkillXPLevelList();
        this.numXPLevels = this.xpLevels.size();
        this.stats = record.getDBStatList();
        this.statBonusRaces = record.getDBStatBonusRaceList();
        this.spawnPets = record.getSpawnPetList();
        this.dependencies = record.getSkillDependencyList();
        this.connections = record.getSkillConnectorList();
    }

    static {
        DBSkill.hashBuffer = new ConcurrentHashMap<String, DBSkill>();
    }

    public static void createTables() throws SQLException {
        final String dropTable = "DROP TABLE GD_SKILL";
        final String createTable = "CREATE TABLE GD_SKILL (SKILL_ID  VARCHAR(256) NOT NULL, REC_CLASS         VARCHAR(64), NAME              VARCHAR(256), DESCRIPTION       VARCHAR(16000), MASTERY           BOOLEAN, DEVOTION          BOOLEAN, MASTERY_ID        VARCHAR(256), SKILL_TIER        INTEGER, MAX_LEVEL         INTEGER, BITMAP_DOWN_ID    VARCHAR(256), BITMAP_DOWN       BLOB(128K), BITMAP_UP_ID      VARCHAR(256), BITMAP_UP         BLOB(128K), LEVEL             INTEGER, DURATION          INTEGER, BONUS_INCREMENT   BOOLEAN, MODIFIED          BOOLEAN, BUFF_SKILL_ID     VARCHAR(256), PET_SKILL_ID      VARCHAR(256), CONVERT_IN        VARCHAR(16), CONVERT_OUT       VARCHAR(16), CONVERT_IN_2      VARCHAR(16), CONVERT_OUT_2     VARCHAR(16), DEPEND_ALL        BOOLEAN, PRIMARY KEY (SKILL_ID))";
        try (final Connection conn = GDDBData.getConnection()) {
            final boolean auto = conn.getAutoCommit();
            conn.setAutoCommit(false);
            try (final Statement st = conn.createStatement()) {
                if (GDDBUtil.tableExists(conn, "GD_SKILL")) {
                    st.execute(dropTable);
                }
                if (GDDBUtil.tableExists(conn, "GD_SKILL_CHAR")) {
                    st.execute("DROP TABLE GD_SKILL_CHAR");
                }
                if (GDDBUtil.tableExists(conn, "GD_SKILL_CHARRACES")) {
                    st.execute("DROP TABLE GD_SKILL_CHARRACES");
                }
                if (GDDBUtil.tableExists(conn, "GD_SKILL_DAMAGE")) {
                    st.execute("DROP TABLE GD_SKILL_DAMAGE");
                }
                st.execute(createTable);
                st.close();
                conn.commit();
                DBStat.createSkillTable(conn);
                DBStatBonusRace.createSkillTable(conn);
                DBSkillSpawn.createTable(conn);
                DBSkillExperience.createTable(conn);
                DBSkillDependency.createTable(conn);
                DBSkillConnector.createTable(conn);
            } catch (SQLException ex) {
                conn.rollback();
                final Object[] args = {"GD_SKILL"};
                final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_CREATE_TABLE", args);
                GDMsgLogger.addError(msg);
                throw ex;
            } finally {
                conn.setAutoCommit(auto);
            }
        }
    }

    private static List<DBSkill> wrap(final ResultSet rs) throws SQLException {
        final LinkedList<DBSkill> list = new LinkedList<DBSkill>();
        Blob blob = null;
        while (rs.next()) {
            final DBSkill skill = new DBSkill();
            skill.skillID = rs.getString(1);
            skill.recClass = rs.getString(2);
            skill.name = rs.getString(3);
            skill.description = rs.getString(4);
            skill.mastery = rs.getBoolean(5);
            skill.devotion = rs.getBoolean(6);
            skill.masteryID = rs.getString(7);
            skill.skillTier = rs.getInt(8);
            skill.maxLevel = rs.getInt(9);
            skill.bitmapDownID = rs.getString(10);
            blob = rs.getBlob(11);
            if (blob == null) {
                skill.bitmapDown = null;
            } else {
                skill.bitmapDown = blob.getBytes(1L, (int) blob.length());
            }
            if (skill.bitmapDown != null) {
                try {
                    skill.imageDown = DDSLoader.getImage(skill.bitmapDown);
                } catch (GDParseException ex) {
                    final Object[] args = {skill.skillID};
                    final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_BITMAP_DECODE_FAILED", args);
                    GDMsgLogger.addError(msg);
                    skill.imageDown = null;
                }
            }
            skill.bitmapUpID = rs.getString(12);
            blob = rs.getBlob(13);
            if (blob == null) {
                skill.bitmapUp = null;
            } else {
                skill.bitmapUp = blob.getBytes(1L, (int) blob.length());
            }
            if (skill.bitmapUp != null) {
                try {
                    skill.imageUp = DDSLoader.getImage(skill.bitmapUp);
                } catch (GDParseException ex) {
                    final Object[] args = {skill.skillID};
                    final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_BITMAP_DECODE_FAILED", args);
                    GDMsgLogger.addError(msg);
                    skill.imageUp = null;
                }
            }
            skill.level = rs.getInt(14);
            skill.duration = rs.getInt(15);
            skill.bonusIncrement = rs.getBoolean(16);
            skill.modified = rs.getBoolean(17);
            skill.buffSkillID = rs.getString(18);
            skill.petSkillID = rs.getString(19);
            skill.convertIn = rs.getString(20);
            skill.convertOut = rs.getString(21);
            skill.convertIn2 = rs.getString(22);
            skill.convertOut2 = rs.getString(23);
            skill.dependencyAll = rs.getBoolean(24);
            skill.xpLevels = DBSkillExperience.getBySkillID(skill.skillID);
            skill.numXPLevels = skill.xpLevels.size();
            Collections.<DBStat>sort(skill.stats = DBStat.getSkill(skill.skillID));
            final DBStat stat = DBStat.getByType(skill.stats, "racialBonusPercentDamage", 1);
            if (stat != null) {
                skill.statBonusRaces = DBStatBonusRace.getSkill(skill.skillID);
                skill.createBonusRaceStats(stat);
            }
            skill.spawnPets = DBSkillSpawn.getBySkillID(skill.skillID);
            skill.dependencies = DBSkillDependency.getBySkillID(skill.skillID);
            skill.connections = DBSkillConnector.getBySkillID(skill.skillID);
            if (skill.masteryID != null && !skill.masteryID.equals(skill.skillID)) {
                skill.dbMastery = get(skill.masteryID);
            }
            if (skill.buffSkillID != null && !skill.buffSkillID.equals(skill.skillID)) {
                skill.dbBuffSkill = get(skill.buffSkillID);
            }
            if (skill.petSkillID != null && !skill.petSkillID.equals(skill.skillID)) {
                skill.dbPetSkill = get(skill.petSkillID);
            }
            if (skill.name != null) {
                final ByteBuffer buffer = StandardCharsets.UTF_8.encode(skill.name);
                final String utf8 = StandardCharsets.UTF_8.decode(buffer).toString();
                skill.name = utf8;
            }
            if (skill.description != null) {
                final ByteBuffer buffer = StandardCharsets.UTF_8.encode(skill.description);
                final String utf8 = StandardCharsets.UTF_8.decode(buffer).toString();
                skill.description = utf8;
            }
            list.add(skill);
        }
        return list;
    }

    public String getSkillID() {
        return this.skillID;
    }

    public String getRecordClass() {
        return this.recClass;
    }

    public boolean isBaseSkill() {
        return !this.recClass.equals("Skill_Transmuter") && !this.recClass.equals("Skill_ProjectileTransmuter") && !this.recClass.equals("Skill_SpawnPetTransmuter") && !this.recClass.equals("Skill_Modifier") && !this.recClass.equals("Skill_ProjectileModifier") && !this.recClass.equals("Skill_RefreshCooldownModifier") && !this.recClass.equals("SkillSecondary_PetModifier") && !this.recClass.startsWith("SkillSecondary_");
    }

    public String getName() {
        if (this.name != null) {
            return this.name;
        }
        if (this.dbBuffSkill != null && this.dbBuffSkill.name != null) {
            return this.dbBuffSkill.name;
        }
        if (this.dbPetSkill != null && this.dbPetSkill.name != null) {
            return this.dbPetSkill.name;
        }
        return null;
    }

    public String getMasteryName() {
        if (this.isMastery()) {
            return this.getName();
        }
        if (this.dbMastery != null) {
            return this.dbMastery.getName();
        }
        return null;
    }

    public String getMasteryID() {
        return this.masteryID;
    }

    public String getDescription() {
        if (this.description != null) {
            return this.description;
        }
        if (this.dbPetSkill != null) {
            return this.dbPetSkill.getDescription();
        }
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getDescription();
        }
        return null;
    }

    public boolean isMastery() {
        return this.mastery;
    }

    public boolean isDevotion() {
        return this.devotion;
    }

    public boolean isDependencyAll() {
        return this.dependencyAll;
    }

    public boolean hasBonusIncrement() {
        return this.bonusIncrement;
    }

    public boolean hasSkillModifier() {
        return this.modified;
    }

    public int getSkillTier() {
        if (this.dbPetSkill != null) {
            final int tier = this.dbPetSkill.getSkillTier();
            if (tier > 0) {
                return tier;
            }
        }
        if (this.dbBuffSkill != null) {
            final int tier = this.dbBuffSkill.getSkillTier();
            if (tier > 0) {
                return tier;
            }
        }
        return this.skillTier;
    }

    public int getMaxLevel() {
        if (this.dbPetSkill != null) {
            final int level = this.dbPetSkill.getMaxLevel();
            if (level > 0) {
                return level;
            }
        }
        if (this.dbBuffSkill != null) {
            final int level = this.dbBuffSkill.getMaxLevel();
            if (level > 0) {
                return level;
            }
        }
        if (this.numXPLevels > 0) {
            return this.numXPLevels;
        }
        return this.maxLevel;
    }

    public BufferedImage getImageDown() {
        if (this.imageDown != null) {
            return this.imageDown;
        }
        if (this.dbPetSkill != null) {
            return this.dbPetSkill.getImageDown();
        }
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getImageDown();
        }
        return null;
    }

    public List<DBSkillDependency> getSkillDependencyList() {
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getSkillDependencyList();
        }
        if (this.dbPetSkill != null) {
            return this.dbPetSkill.getSkillDependencyList();
        }
        return this.dependencies;
    }

    public List<DBSkillConnector> getSkillConnectionList() {
        if (!this.connections.isEmpty()) {
            return this.connections;
        }
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getSkillConnectionList();
        }
        if (this.dbPetSkill != null) {
            return this.dbPetSkill.getSkillConnectionList();
        }
        return null;
    }

    public BufferedImage getImageUp() {
        if (this.imageUp != null) {
            return this.imageUp;
        }
        if (this.dbPetSkill != null) {
            return this.dbPetSkill.getImageUp();
        }
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getImageUp();
        }
        return null;
    }

    public int getSkillLevel() {
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.level;
        }
        return this.level;
    }

    public int getSkillDuration() {
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.duration;
        }
        return this.duration;
    }

    public int getXPForLevel(final int level) {
        List<Integer> list = null;
        if (this.dbBuffSkill != null) {
            list = this.dbBuffSkill.xpLevels;
        } else {
            list = this.xpLevels;
        }
        int lvl = 1;
        int xp = 0;
        for (final int i : list) {
            if (lvl == level) {
                xp = i;
                break;
            }
            ++lvl;
        }
        return xp;
    }

    public DBPet getPet(final int index) {
        DBPet pet = null;
        int i = 0;
        for (final DBSkillSpawn spawn : this.spawnPets) {
            pet = spawn.getPet();
            ++i;
            if (i == index) {
                break;
            }
        }
        return pet;
    }

    public int getManaCost(final int index) {
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getManaCost(index);
        }
        if (this.dbPetSkill != null) {
            return this.dbPetSkill.getManaCost(index);
        }
        final DBStat stat = DBStat.getDBStat(this.stats, "skillManaCost", index);
        if (stat == null) {
            return 0;
        }
        return (int) stat.getStatMin();
    }

    public float getCooldownTime(final int index) {
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getCooldownTime(index);
        }
        if (this.dbPetSkill != null) {
            return this.dbPetSkill.getCooldownTime(index);
        }
        final DBStat stat = DBStat.getDBStat(this.stats, "skillCooldownTime", index);
        if (stat == null) {
            return 0.0f;
        }
        return stat.getStatMin();
    }

    public int getActiveDuration(final int index) {
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getActiveDuration(index);
        }
        if (this.dbPetSkill != null) {
            return this.dbPetSkill.getActiveDuration(index);
        }
        if (this.duration > 0) {
            return this.duration;
        }
        final DBStat stat = DBStat.getDBStat(this.stats, "skillActiveDuration", index);
        if (stat == null) {
            return 0;
        }
        return (int) stat.getStatMin();
    }

    public int getTriggerLifePerc(final int index) {
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getTriggerLifePerc(index);
        }
        final DBStat stat = DBStat.getDBStat(this.stats, "lifeMonitorPercent", index);
        if (stat == null) {
            return 0;
        }
        return (int) stat.getStatMin();
    }

    public int getWeaponDamagePerc(final int index) {
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.getWeaponDamagePerc(index);
        }
        final DBStat stat = DBStat.getDBStat(this.stats, "weaponDamagePct", index);
        if (stat == null) {
            return 0;
        }
        return (int) stat.getStatMin();
    }

    public String getConvertIn() {
        return this.convertIn;
    }

    public String getConvertOut() {
        return this.convertOut;
    }

    public String getConvertIn2() {
        return this.convertIn2;
    }

    public String getConvertOut2() {
        return this.convertOut2;
    }

    public int getConvertPerc() {
        return this.getConvertPerc(1);
    }

    public int getConvertPerc(final int level) {
        final DBStat stat = DBStat.getDBStat(this.stats, "conversionPercentage", level);
        if (stat == null) {
            return 0;
        }
        return (int) stat.getStatMin();
    }

    public int getConvertPerc2() {
        return this.getConvertPerc2(1);
    }

    public int getConvertPerc2(final int level) {
        final DBStat stat = DBStat.getDBStat(this.stats, "conversionPercentage2", level);
        if (stat == null) {
            return 0;
        }
        return (int) stat.getStatMin();
    }

    public List<DBStat> getStatList() {
        return this.getStatList(null, SkillDetail.ALL, false);
    }

    public List<DBStat> getStatList(final String[] statExclude, final SkillDetail detail) {
        return this.getStatList(statExclude, detail, false);
    }

    public List<DBStat> getStatList(final String[] statExclude, final SkillDetail detail, final boolean petStat) {
        final List<DBStat> list = new LinkedList<DBStat>();
        if ((petStat && detail != SkillDetail.SKILL) || (!petStat && detail != SkillDetail.PET)) {
            for (final DBStat stat : this.stats) {
                boolean exclude = false;
                if (statExclude != null) {
                    for (final String se : statExclude) {
                        if (stat.getStatType().equals(se)) {
                            exclude = true;
                            break;
                        }
                    }
                }
                if (exclude) {
                    continue;
                }
                if (detail == SkillDetail.PET) {
                    stat.setPetStat(false);
                } else {
                    stat.setPetStat(petStat);
                }
                list.add(stat);
            }
        }
        if (this.dbBuffSkill != null) {
            list.addAll(this.dbBuffSkill.getStatList(statExclude, detail, petStat));
        }
        if (this.dbPetSkill != null) {
            list.addAll(this.dbPetSkill.getStatList(statExclude, detail, true));
        }
        return list;
    }

    public List<DBStat> getStatListNoPet() {
        if (this.dbBuffSkill != null) {
            return this.dbBuffSkill.stats;
        }
        return this.stats;
    }

    public String getBuffSkillID() {
        return this.buffSkillID;
    }

    public static void clearBuffer() {
        DBSkill.hashBuffer.clear();
    }

    public DBSkill getBuffSkill() {
        return this.dbBuffSkill;
    }

    public static void delete(final String skillID) throws SQLException {
        final String deleteEntry = "DELETE FROM GD_SKILL WHERE SKILL_ID = ?";
        try (final Connection conn = GDDBData.getConnection()) {
            final boolean auto = conn.getAutoCommit();
            conn.setAutoCommit(false);
            try (final PreparedStatement ps = conn.prepareStatement(deleteEntry)) {
                ps.setString(1, skillID);
                ps.executeUpdate();
                ps.close();
                DBStat.deleteSkill(conn, skillID);
                DBStatBonusRace.deleteSkill(conn, skillID);
                DBSkillExperience.delete(conn, skillID);
                DBSkillConnector.delete(conn, skillID);
                conn.commit();
            } catch (SQLException ex) {
                conn.rollback();
                final Object[] args = {skillID, "GD_SKILL"};
                final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_DEL_TABLE_BY_ID", args);
                GDMsgLogger.addError(msg);
                GDMsgLogger.addError(ex);
                throw ex;
            } finally {
                conn.setAutoCommit(auto);
            }
        } catch (SQLException ex2) {
            throw ex2;
        }
    }

    public static void insert(final ARZRecord record) throws SQLException {
        final DBSkill entry = get(record.getFileName());
        if (entry != null) {
            return;
        }
        final DBSkill skill = new DBSkill(record);
        final String insert = "INSERT INTO GD_SKILL VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try (final Connection conn = GDDBData.getConnection()) {
            final boolean auto = conn.getAutoCommit();
            conn.setAutoCommit(false);
            try (final PreparedStatement ps = conn.prepareStatement(insert)) {
                ps.setString(1, skill.skillID);
                ps.setString(2, skill.recClass);
                ps.setString(3, skill.name);
                ps.setString(4, skill.description);
                ps.setBoolean(5, skill.mastery);
                ps.setBoolean(6, skill.devotion);
                ps.setString(7, skill.masteryID);
                ps.setInt(8, skill.skillTier);
                ps.setInt(9, skill.maxLevel);
                ps.setString(10, skill.bitmapDownID);
                if (skill.bitmapDown == null) {
                    ps.setBlob(11, (Blob) null);
                } else {
                    ps.setBlob(11, new ByteArrayInputStream(skill.bitmapDown));
                }
                ps.setString(12, skill.bitmapUpID);
                if (skill.bitmapUp == null) {
                    ps.setBlob(13, (Blob) null);
                } else {
                    ps.setBlob(13, new ByteArrayInputStream(skill.bitmapUp));
                }
                ps.setInt(14, skill.level);
                ps.setInt(15, skill.duration);
                ps.setBoolean(16, skill.bonusIncrement);
                ps.setBoolean(17, skill.modified);
                ps.setString(18, skill.buffSkillID);
                ps.setString(19, skill.petSkillID);
                ps.setString(20, skill.convertIn);
                ps.setString(21, skill.convertOut);
                ps.setString(22, skill.convertIn2);
                ps.setString(23, skill.convertOut2);
                ps.setBoolean(24, skill.dependencyAll);
                ps.executeUpdate();
                ps.close();
                DBStat.insertSkill(conn, skill.skillID, skill.stats);
                DBStatBonusRace.insertSkill(conn, skill.skillID, skill.statBonusRaces);
                DBSkillSpawn.insert(conn, skill.skillID, skill.spawnPets);
                DBSkillExperience.insert(conn, skill.skillID, skill.xpLevels);
                DBSkillDependency.insert(conn, skill.skillID, skill.dependencies);
                DBSkillConnector.insert(conn, skill.skillID, skill.connections);
                conn.commit();
            } catch (SQLException ex) {
                conn.rollback();
                final Object[] args = {record.getFileName(), "GD_SKILL"};
                final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_INS_TABLE_BY_ID", args);
                if (skill.isDevotion()) {
                    GDMsgLogger.addError(msg);
                    GDMsgLogger.addError(ex);
                } else {
                    GDMsgLogger.addLowError(msg);
                    GDMsgLogger.addLowError(ex);
                }
            } finally {
                conn.setAutoCommit(auto);
            }
        }
    }

    public static DBSkill get(final String skillID) {
        if (skillID == null) {
            return null;
        }
        DBSkill skill = null;
        skill = DBSkill.hashBuffer.get(skillID);
        if (skill == null) {
            skill = getDB(skillID);
            if (skill != null) {
                DBSkill.hashBuffer.put(skill.skillID, skill);
            }
        }
        return skill;
    }

    public static List<DBSkill> getAll() {
        final String command = "SELECT SKILL_ID FROM GD_SKILL";
        final List<DBSkill> skills = new LinkedList<DBSkill>();
        final List<String> skillIDs = new LinkedList<String>();
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            try (final ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    final String s = rs.getString(1);
                    skillIDs.add(s);
                }
            } catch (SQLException ex) {
                throw ex;
            }
            conn.commit();
            for (final String skillID : skillIDs) {
                final DBSkill skill = get(skillID);
                skills.add(skill);
            }
        } catch (SQLException ex2) {
            final Object[] args = {"-", "GD_SKILL"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return skills;
    }

    public static List<DBSkill> getBonusSkills() {
        final String command = "SELECT SKILL_ID FROM GD_SKILL WHERE BONUS_INCREMENT = true ORDER BY MASTERY DESC, NAME ASC";
        final List<DBSkill> skills = new LinkedList<DBSkill>();
        final List<String> skillIDs = new LinkedList<String>();
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            try (final ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    final String s = rs.getString(1);
                    skillIDs.add(s);
                }
            } catch (SQLException ex) {
                throw ex;
            }
            conn.commit();
            for (final String skillID : skillIDs) {
                final DBSkill skill = get(skillID);
                skills.add(skill);
            }
        } catch (SQLException ex2) {
            final Object[] args = {"-", "GD_SKILL"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return skills;
    }

    public static List<DBSkill> getModifiedSkills() {
        final String command = "SELECT SKILL_ID FROM GD_SKILL WHERE MODIFIED = true ORDER BY MASTERY DESC, NAME ASC";
        final List<DBSkill> skills = new LinkedList<DBSkill>();
        final List<String> skillIDs = new LinkedList<String>();
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            try (final ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    final String s = rs.getString(1);
                    skillIDs.add(s);
                }
            } catch (SQLException ex) {
                throw ex;
            }
            conn.commit();
            for (final String skillID : skillIDs) {
                final DBSkill skill = get(skillID);
                skills.add(skill);
            }
        } catch (SQLException ex2) {
            final Object[] args = {"-", "GD_SKILL"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return skills;
    }

    private static DBSkill getDB(final String skillID) {
        DBSkill skill = null;
        final String command = "SELECT * FROM GD_SKILL WHERE SKILL_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            ps.setString(1, skillID);
            try (final ResultSet rs = ps.executeQuery()) {
                final List<DBSkill> list = wrap(rs);
                if (list.isEmpty()) {
                    skill = null;
                } else {
                    skill = list.get(0);
                }
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {skillID, "GD_SKILL"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return skill;
    }

    public String getPetSkillID() {
        return this.petSkillID;
    }

    public DBSkill getPetSkill() {
        return this.dbPetSkill;
    }

    public static List<ImageInfo> getImageInfos(final String id) {
        final List<ImageInfo> list = new LinkedList<ImageInfo>();
        Blob blob = null;
        final String command = "SELECT SKILL_ID, BITMAP_DOWN_ID, BITMAP_DOWN, BITMAP_UP_ID, BITMAP_UP FROM GD_SKILL WHERE ((BITMAP_DOWN_ID LIKE '" + id + "%' AND BITMAP_DOWN IS NULL) OR (BITMAP_UP_ID LIKE '" + id + "%' AND BITMAP_UP IS NULL))";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            try (final ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    final ImageInfo info = new ImageInfo();
                    info.skillID = rs.getString(1);
                    info.bitmapDownID = rs.getString(2);
                    blob = rs.getBlob(3);
                    if (blob == null) {
                        info.bitmapDown = null;
                    } else {
                        info.bitmapDown = blob.getBytes(1L, (int) blob.length());
                    }
                    info.bitmapUpID = rs.getString(4);
                    blob = rs.getBlob(5);
                    if (blob == null) {
                        info.bitmapUp = null;
                    } else {
                        info.bitmapUp = blob.getBytes(1L, (int) blob.length());
                    }
                    list.add(info);
                }
                conn.commit();
            } catch (Exception ex) {
                throw ex;
            }
        } catch (Exception ex2) {
            GDMsgLogger.addError(ex2);
        }
        return list;
    }

    public static void updateImageInfo(final List<ImageInfo> list) {
        if (list == null) {
            return;
        }
        final String command = "UPDATE GD_SKILL SET BITMAP_DOWN = ?, BITMAP_UP = ? WHERE SKILL_ID = ?";
        try (final Connection conn = GDDBData.getConnection()) {
            final boolean auto = conn.getAutoCommit();
            conn.setAutoCommit(false);
            for (final ImageInfo info : list) {
                try (final PreparedStatement ps = conn.prepareStatement(command)) {
                    if (info.bitmapDown != null || info.bitmapUp != null) {
                        if (info.bitmapDown == null) {
                            ps.setBlob(1, (Blob) null);
                        } else {
                            ps.setBlob(1, new ByteArrayInputStream(info.bitmapDown));
                        }
                        if (info.bitmapUp == null) {
                            ps.setBlob(2, (Blob) null);
                        } else {
                            ps.setBlob(2, new ByteArrayInputStream(info.bitmapUp));
                        }
                        ps.setString(3, info.skillID);
                        ps.executeUpdate();
                        ps.close();
                        conn.commit();
                    }
                } catch (SQLException ex) {
                    conn.rollback();
                    final Object[] args = {info.skillID};
                    final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_IN_ITEM_IMAGE_SIZE", args);
                    GDMsgLogger.addWarning(msg);
                    GDMsgLogger.addWarning(ex);
                }
            }
        } catch (SQLException ex2) {
            GDMsgLogger.addError(ex2);
        }
    }

    public static void updateDB() {
        setMasteryID();
        updateBonusIncFlag();
        updateModifiedFlag();
        updateSkillNames();
    }

    public static void setMasteryID() {
        final String command = "UPDATE GD_SKILL SET MASTERY_ID = ? WHERE SKILL_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            final List<DBSkillTree> trees = DBSkillTree.getMasteryTrees();
            final List<DBSkill> skills = getAll();
            for (final DBSkill skill : skills) {
                for (final DBSkillTree tree : trees) {
                    if (tree.getSkillIDList().contains(skill.skillID)) {
                        final DBSkill mastery = tree.getMasterySkill();
                        if (mastery != null) {
                            ps.setString(1, mastery.getSkillID());
                            ps.setString(2, skill.skillID);
                            ps.executeUpdate();
                            ps.clearParameters();
                            break;
                        }
                        break;
                    }
                }
            }
            ps.close();
            conn.commit();
        } catch (SQLException ex) {
            final Object[] args = {"-", "GD_SKILL"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex);
        }
    }

    public static void updateBonusIncFlag() {
        final List<String> list = new LinkedList<String>();
        try {
            final List<DBSkillBonus> bonuses = DBSkillBonus.getAllItem();
            if (bonuses != null) {
                for (final DBSkillBonus bonus : bonuses) {
                    final String skillID = bonus.getEntity();
                    if (skillID != null && !list.contains(skillID)) {
                        list.add(skillID);
                    }
                }
            }
        } catch (SQLException ex) {
        }
        try {
            final List<DBSkillBonus> bonuses = DBSkillBonus.getAllAffix();
            if (bonuses != null) {
                for (final DBSkillBonus bonus : bonuses) {
                    final String skillID = bonus.getEntity();
                    if (skillID != null && !list.contains(skillID)) {
                        list.add(skillID);
                    }
                }
            }
        } catch (SQLException ex2) {
        }
        updateBonusIncFlag(list);
    }

    private static void updateBonusIncFlag(final List<String> list) {
        final String command = "UPDATE GD_SKILL SET BONUS_INCREMENT = true WHERE SKILL_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            for (final String skillID : list) {
                if (skillID == null) {
                    continue;
                }
                ps.setString(1, skillID);
                ps.executeUpdate();
                ps.clearParameters();
            }
            ps.close();
            conn.commit();
        } catch (SQLException ex) {
            final Object[] args = {"-", "GD_SKILL"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex);
        }
    }

    public static void updateModifiedFlag() {
        final List<String> list = new LinkedList<String>();
        try {
            final List<DBSkillModifier> modifiers = DBSkillModifier.getAllItem();
            if (modifiers != null) {
                for (final DBSkillModifier modifier : modifiers) {
                    final String skillID = modifier.getSkillID();
                    if (!list.contains(skillID)) {
                        list.add(skillID);
                    }
                }
            }
        } catch (SQLException ex) {
        }
        try {
            final List<DBSkillModifier> modifiers = DBSkillModifier.getAllAffix();
            if (modifiers != null) {
                for (final DBSkillModifier modifier : modifiers) {
                    final String skillID = modifier.getSkillID();
                    if (!list.contains(skillID)) {
                        list.add(skillID);
                    }
                }
            }
        } catch (SQLException ex2) {
        }
        updateModifiedFlag(list);
    }

    private static void updateModifiedFlag(final List<String> list) {
        final String command = "UPDATE GD_SKILL SET MODIFIED = true WHERE SKILL_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            for (final String skillID : list) {
                if (skillID == null) {
                    continue;
                }
                ps.setString(1, skillID);
                ps.executeUpdate();
                ps.clearParameters();
            }
            ps.close();
            conn.commit();
        } catch (SQLException ex) {
            final Object[] args = {"-", "GD_SKILL"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex);
        }
    }

    public static void updateSkillNames() {
        boolean changed = true;
        while (changed) {
            changed = false;
            final List<DBSkill> list = getEmptyNames();
            if (list == null) {
                continue;
            }
            for (final DBSkill skill : list) {
                if (skill == null) {
                    continue;
                }
                String id = null;
                if (skill.getPetSkillID() != null) {
                    id = skill.getPetSkillID();
                }
                if (skill.getBuffSkillID() != null) {
                    id = skill.getBuffSkillID();
                }
                if (id == null) {
                    continue;
                }
                final DBSkill dbs = get(id);
                if (dbs == null) {
                    final int pos = skill.skillID.indexOf("/old/");
                    if (pos != -1) {
                        continue;
                    }
                    final Object[] args = {"GD_SKILL", skill.skillID, id, "GD_SKILL"};
                    final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_REF_TABLE_ID_FROM_ID", args);
                    GDMsgLogger.addWarning(msg);
                } else {
                    skill.name = dbs.getName();
                    if (skill.name == null) {
                        continue;
                    }
                    changed = true;
                }
            }
            if (!changed) {
                continue;
            }
            updateNames(list);
        }
    }

    private static List<DBSkill> getEmptyNames() {
        List<DBSkill> list = new LinkedList<DBSkill>();
        final String command = "SELECT * FROM GD_SKILL WHERE NAME is null AND ((BUFF_SKILL_ID is not null) OR (PET_SKILL_ID is not null))";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            try (final ResultSet rs = ps.executeQuery()) {
                list = wrap(rs);
                conn.commit();
            } catch (SQLException ex) {
                throw ex;
            }
        } catch (SQLException ex2) {
            final Object[] args = {"NAME is null", "GD_SKILL"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex2);
        }
        return list;
    }

    private static void updateNames(final List<DBSkill> list) {
        final String command = "UPDATE GD_SKILL SET NAME = ? WHERE SKILL_ID = ?";
        try (final Connection conn = GDDBData.getConnection();
             final PreparedStatement ps = conn.prepareStatement(command)) {
            for (final DBSkill skill : list) {
                if (skill.name == null) {
                    continue;
                }
                ps.setString(1, skill.name);
                ps.setString(2, skill.skillID);
                ps.executeUpdate();
                ps.clearParameters();
            }
            ps.close();
            conn.commit();
        } catch (SQLException ex) {
            final Object[] args = {"-", "GD_SKILL"};
            final String msg = GDMsgFormatter.format(GDMsgFormatter.rbMsg, "ERR_READ_TABLE_BY_ID", args);
            GDMsgLogger.addError(msg);
            GDMsgLogger.addError(ex);
        }
    }

    public BonusDetail getBonusDetail() {
        final BonusDetail bonus = new BonusDetail(this);
        return bonus;
    }

    @Override
    public String toString() {
        String s = this.name;
        if (this.isMastery()) {
            s = s + " (" + GDMsgFormatter.getString(GDMsgFormatter.rbGD, "TXT_MASTERY") + ")";
        } else {
            final String m = this.getMasteryName();
            if (m != null) {
                s = s + " (" + m + ")";
            }
        }
        return s;
    }

    public String getSkillDescription(final int level, final int charLevel) {
        return DetailComposer.getSkillDescription(this, level, charLevel, false, true);
    }

    public String getSkillNextLevelDescription(final int level, final int charLevel) {
        return DetailComposer.getSkillDescription(this, level, charLevel, true, true);
    }

    public String getSkillDescription(final int level, final int charLevel, final boolean fullHTML) {
        return DetailComposer.getSkillDescription(this, level, charLevel, false, fullHTML);
    }

    private void createBonusRaceStats(final DBStat stat) {
        if (stat == null) {
            return;
        }
        if (this.statBonusRaces == null) {
            return;
        }
        if (this.statBonusRaces.isEmpty()) {
            return;
        }
        this.stats.remove(stat);
        final List<DBStat> list = DBStat.createStatsFromRaceBonusList(stat, this.statBonusRaces);
        this.stats.addAll(list);
    }

    public enum SkillDetail {
        ALL,
        SKILL,
        PET;
    }

    public static class ImageInfo {
        public String skillID;
        public String bitmapDownID;
        public String bitmapUpID;
        public byte[] bitmapDown;
        public byte[] bitmapUp;
    }
}
